package com.springboot.demo.mapper;

import com.fasterxml.jackson.annotation.JsonFormat;
import com.springboot.demo.entity.*;
import com.springboot.demo.entity.validate.device.DevClusterDescGroup;
import com.springboot.demo.entity.validate.device.DevClusterNameGroup;
import com.springboot.demo.entity.validate.user.IdGroup;
import com.springboot.demo.entity.vo.DeviceClusterUser;
import com.springboot.demo.entity.vo.UserDeviceVO;
import io.swagger.annotations.ApiModelProperty;
import org.apache.ibatis.annotations.*;
import org.hibernate.validator.constraints.Length;
import org.springframework.format.annotation.DateTimeFormat;

import javax.validation.constraints.*;
import java.util.Date;
import java.util.List;

/**
 * @author wubc683
 * @desc
 * @date 2021-09-13 9:51
 */
public interface DeviceMapper {
    @Select("select id, name, description,  createDate from web_device_cluster where id = #{cid}")
    DeviceCluster getDevClusterInfo(Long cid);

    @Select("select dc.id as id,dc.name as name,dc.description as description, dc.createDate as createDate,udc.role" +
            " as role" + " from web_device_cluster dc" + " left join " +
            "web_user_device_cluster udc on dc.id = udc.deviceClusterId where " +
            "udc.userId = #{userId} and dc.id = #{cid}")
    DeviceCluster getDevClusterInfoAndUserRole(Long userId, Long cid);

    @SelectProvider(type = SqlMapper.class, method = "selectDevClusterDevInfo")
    List<Device> getDevClusterDevInfo(Long cid, String search, Integer state, String sort);

    @Insert("insert into web_device_cluster(name,description,createDate) values(#{name},#{description},#{createDate})")
    @Options(useGeneratedKeys = true, keyProperty = "id", keyColumn = "id")
    void saveDevClusterInfo(DeviceCluster deviceCluster);

    @Select("select id,name,description,createDate from web_device_cluster where name = #{name}")
    DeviceCluster getDevClusterInfoByName(String name);

    @Select("select ID from T_DeviceCluster where account = #{name}")
    Long getDevClusterIdByNameFromMico(String name);

    @Select("select ID from T_DeviceCluster where account = #{name} and account != #{oldName}")
    Long getDevClusterIdByOldNameAndNameFromMico(String oldName, String name);

    @Insert("insert into T_DeviceCluster(type,account,creatorID,sysAdminID,describ,createDate) values(0,#{name}," +
            "0,0,#{description},#{createDate})")
    void saveDevClusterInfoInMico(DeviceCluster devClu);

    @Select("select id,name,description,createDate from web_device_cluster where id = #{clusterId}")
    DeviceCluster getDevClusterInfoById(Long clusterId);

    @Select("select dc.id as id,dc.name as name,dc.description as description, dc.createDate as createDate,udc.role" +
            " as role" + " from web_device_cluster dc" + " left join " +
            "web_user_device_cluster udc on dc.id = udc.deviceClusterId where " +
            "udc.userId = #{ownerId}")
    List<DeviceCluster> getAllDevClusterInfo(Long ownerId);

    @Insert("insert into web_user_device_cluster(userId,deviceClusterId,role) values(#{ownerId},#{cid}, #{role})")
    void saveUserDevClusterInfo(Long ownerId, Long cid, Integer role);

    @Select("select u.id,u.username,udc.role from web_user u ,web_user_device_cluster udc where u.id = udc.userId and" +
            " udc.deviceClusterId = #{cid}" + " and u.deleteFlag = '' and u.id in (select userId from " +
            "web_user_device_cluster  where " +
            " deviceClusterId = #{cid})")
    List<DeviceClusterUser> getDevClusterUserInfo(Long ownerId, Long cid);

    @Select("select role from web_user_device_cluster where userId = #{ownerId} and deviceClusterId = #{clusterId}")
    Integer getDevClusterUserRole(Long ownerId, Long clusterId);

    @Update("update web_device_cluster set name = #{name},description = #{description} where id = #{id}")
    void updateDevCluster(DeviceCluster deviceCluster);

    @Update("update T_DeviceCluster set account = #{name},describ = #{description} where account = #{oldName}")
    void updateDevClusterinMico(String oldName, String name, String description);

    @Delete("delete from web_device_cluster where id = #{clusterId}")
    void delDevClusterInfo(Long clusterId);

    @Delete("delete from web_user_device_cluster where deviceClusterId = #{clusterId}")
    void delDevClusterUserInfo(Long clusterId);

    @Delete("delete from T_DeviceCluster where account = #{name}")
    void delDevClusterInfoInMico(String name);

    @Update("update web_user_device_cluster set role = #{role} where userId = #{ownerId} and deviceClusterId = " +
            "#{clusterId}")
    void updateDevClusterUserInfo(Long ownerId, Long clusterId, Integer role);

    @Select("select id,micoDeviceId,deviceClusterId,deviceName,macAddr,description,createDate,online,address,province,city," +
            "region,longitude,latitude from web_device where deviceClusterId = #{clusterId} and deleteFlag = 0")
    List<Device> getDeviceInfoByCid(Long clusterId);

    @Select("select id from web_device where deviceClusterId = #{clusterId} and deleteFlag = 0")
    List<Long> getDeviceIdByCid(Long clusterId);

    @Select("select deviceName from web_device where id = #{id} and deleteFlag = 0")
    String getDevNameById(Long id);

    @Select({"<script>" + "select deviceName from web_device where deleteFlag = 0 and id in " + "<foreach item='item'" +
            " " + "collection='ids' " +
            "open='(' separator=',' close=')'> #{item} </foreach> " + "</script>"})
    List<String> getDevNamesByIds(List<Long> ids);

    @Select("select userId from web_user_device_cluster where deviceClusterId = #{clusterId} and role = #{role}")
    Long getDevClusterUserId(Long clusterId, Integer role);

    @Update("update web_device set deviceClusterId = #{cid} where id = #{id}")
    void updateDevDevCluster(Long id, Long cid);

    @Select("select deviceClusterId from web_user_device_cluster where userId = #{userId}")
    List<Long> getDevclusterIdByUserId(Long userId);

    @Select("select d.id as id,micoDeviceId,deviceClusterId,dc.name as deviceClusterName,deviceName,macAddr,d" +
            ".description as description," +
            "d.createDate as createDate,online,address,province,city," +
            "region,longitude,latitude ,deleteFlag from web_device d inner join web_device_cluster dc on d" +
            ".deviceClusterId = dc.id where d.id = #{deviceId} and d" + ".deleteFlag = 0")
    Device getDeviceInfoById(Long deviceId);

    @Insert("insert into web_user_device(userId,deviceId) values(#{userId},#{deviceId})")
    void saveUserDevice(Long userId, Long deviceId);

    @Select("select deviceClusterId from web_device where id = #{deviceId} and deleteFlag = 0")
    Long getDevClusterIdByDeviceId(Long deviceId);

    @Select("select deviceId from web_user_device where userId = #{userId} and deviceId = #{deviceId}")
    Long getDeviceIdByUserIdAndDeviceId(Long userId, Long deviceId);

    @Delete("delete from web_user_device where deviceId = #{deviceId}")
    void delUserDeviceByDevId(Long deviceId);

    @Delete("delete from web_device where id = #{deviceId}")
    void delDevFromDevCluster(Long deviceId);

    @Update("update T_Device set status = #{deleted} where id = #{micoDeviceId}")
    void updateDevStatus(Long micoDeviceId, Integer deleted);

    @Delete("delete from web_user_device where userId = #{userId} and deviceId = #{deviceId}")
    void delUserDeviceByUserIdAndDevId(Long userId, Long deviceId);

    @Select("select userId from web_user_device_cluster where deviceClusterId = #{deviceClusterId}")
    List<Long> getDevClusterUserIds(Long deviceClusterId);

    @Delete("delete from web_user_device_cluster where userId = #{uid} and deviceClusterId = #{cid}")
    void delDevClusterUserByUserIdAndDevClusterId(Long cid, Long uid);

    @Delete("delete from web_user_device where userId = #{uid} and deviceId in (select id from web_device where " +
            "deviceClusterId = #{cid} and deleteFlag = 0)")
    void delUserDeviceByDevClusterIdAndUserId(Long cid, Long uid);

    @Delete("delete from web_user_device_cluster where userId = #{userId} and deviceClusterId = #{cid}")
    void delDevClusterUser(Long userId, Long cid);

    @SelectProvider(type = SqlMapper.class, method = "getDevClusterInfo")
    List<DeviceCluster> getDevClusterInfoByNameAndRole(Long userId, String name);

    @Select("select id,micoDeviceId,deviceClusterId,deviceName,macAddr,description,createDate,online,address,province,city," +
            "region,longitude,latitude ,deleteFlag from web_device where deleteFlag = 0 and id in (select deviceId " +
            "from web_user_device" + " " + "where userId = #{userId})")
    List<Device> getMyDeviceInfo(Long userId);

    @Delete("delete from web_user_device where userId = #{userId} and deviceId = #{deviceId}")
    void delMyDevice(Long userId, Long deviceId);

    @Select("select role from web_user_device_cluster where userId = #{ownerId} and deviceClusterId = (select " +
            "deviceClusterId from web_device where id = #{deviceId})")
    Integer getDevClusterUserRoleByUserIdAndDevId(Long ownerId, Long deviceId);

    @Update("update web_device set  description = #{description},address = #{address}," +
            "address=#{address},province=#{province},city=#{city},region=#{region},longitude=#{longitude}," +
            "latitude=#{latitude} where id = #{id}")
    void updateDeviceInfo(Device device);

    @Select("select id,micoDeviceId,deviceClusterId,deviceName,macAddr,description,createDate,online,address,province,city," +
            "region,longitude,latitude ,deleteFlag from web_device where deviceClusterId = #{clusterId} and " +
            "deleteFlag = 0")
    List<Device> getDevClusterAllDevInfo(Long clusterId);

    @Select("select count(*) from web_user_device_cluster where userId = #{ownerId}")
    Integer getDevClusterCount(Long ownerId);

    @Update("<script>" + " update web_device set deleteFlag = #{status} where id in " + "<foreach " +
            "item = " + "'item' " + "collection='deviceIds' open=" +
            "'(' separator = ',' close=')'> #{item}" + "</foreach>" + " </script>")
    void updateDevFromDevClusterByIds(List<Long> deviceIds, Integer status);

    @Delete("<script> delete from web_user_device where deviceId in " + "<foreach item = 'item' " +
            "collection='deviceIds' " + "open='(' " + "separator = ',' close=')'> #{item}" + "</foreach> </script>")
    void delUserDeviceByDevIds(List<Long> deviceIds);

    @Update("<script> update web_device set deviceClusterId = #{cid} where id in <foreach item = 'item' " +
            "collection='deviceIds' open='(' separator = ',' close=')'>" + "#{item} </foreach> </script>")
    void updateDevDevClusterByIds(Long cid, List<Long> deviceIds);

    @Update("<script> update T_Device set clusterID = (select ID from T_DeviceCluster where account = #{name})" +
            " where ID in (select micoDeviceId from web_device where id in " + "<foreach item = 'item' " +
            "collection='deviceIds' open='(' separator = ',' close=')'> #{item} </foreach> )</script>")
    void updateDevDevClusterFromMicoByIds(String name, List<Long> deviceIds);

    @SelectProvider(type = SqlMapper.class, method = "getMicoDevInfo")
    List<MicoDevice> getMicoDevInfo(String username, String sort, String search);

    @Select("select id,name,description,createDate from web_device_cluster where id in " +
            "(select deviceClusterId from web_user_device_cluster " +
            "where userId = #{userId})")
    List<DeviceCluster> getDevClusterInfoByUserId(Long userId);

    @Select("select id,micoDeviceId,deviceClusterId,deviceName,macAddr,description,createDate,online,address,province,city," +
            "region,longitude,latitude ,deleteFlag from web_device where deleteFlag = 0")
    List<Device> getAllDeviceInfo();

    @Insert("insert into web_device(micoDeviceId,deviceClusterId,deviceName,macAddr,description,createDate,online) " +
            "values" +
            "(#{micoDeviceId},#{deviceClusterId},#{deviceName},#{macAddr},#{description},#{createDate},#{online})")
    void saveDevice(Device device);

    @Select("select deviceId from web_user_device where userId = #{uid}")
    List<Long> getMyDeviceIdByUid(Long uid);

    @Select("select userId from web_user_device_cluster where deviceClusterId = #{cid} and role = #{owner}")
    Long getDevClusterOwnerIdByCidAndRole(Long cid, Integer owner);

    @Select("select role from web_user_device_cluster where deviceClusterId = #{cid} and userId = #{uid}")
    Integer getDevClusterRoleByUidAndCid(Long uid, Long cid);

    @Select("select id,username from web_user where id in (select userId from web_user_device_cluster where " +
            "deviceClusterId = #{cid} and " + "role < " + "#{user})")
    List<User> getDevClusterOwnerAndAdminUserInfo(Long cid, Integer user);

//    @Select("select id from web_device where micoDeviceId = #{deviceId} and deleteFlag = 0")
//    Long getDeviceInfoByMicoDevId(Long deviceId);
//
//    @Update("<script> update web_device set deleteFlag = 1 ,online = 0 where id in <foreach item='item' " +
//            "collection='deviceIdList' open='(' separator=',' close=')'>#{item}</foreach> </script>")
//    void updateDeviceListDeleted(List<Long> deviceIdList);

    @Update("update web_device set online = #{status},deleteFlag = 0 where micoDeviceId = #{deviceId} and (select " +
            "tmp.name from" + "(" +
            " select distinct dc.name as name from web_device_cluster dc inner join web_device d on d.deviceClusterId" +
            " = dc.id" + " and d" + ".micoDeviceId" +
            " = #{deviceId}) tmp) = (select distinct tdc.account from T_DeviceCluster tdc inner join T_Device td on " +
            "td" +
            ".clusterID = " +
            "tdc.ID and td.ID = #{deviceId})")
    void updateDeviceLogin(Long deviceId, Integer status);

    @Update("update web_device set online = #{status} where micoDeviceId = #{deviceId}")
    void updateDeviceOffline(Long deviceId, Integer status);

//    @Select("select distinct u.id,u.username from web_user u left join web_user_device_cluster udc on u.id = udc" +
//            ".userId left join web_device d on " +
//            " udc.deviceClusterId = d.deviceClusterId left join web_user_device ud on d.id = ud.deviceId" +
//            " where d.micoDeviceId = #{micoDeviceId} and (udc.role = #{owner} or ud.userId is not null)")
//    List<User> getUserByMicoDeviceId(Long micoDeviceId, Integer owner);
//
//    @Select("select deviceName from web_device where micoDeviceId = #{deviceId} and deleteFlag = 0")
//    String getDeviceNameByMicoDeviceId(Long deviceId);

    @Select("<script> select s.id,s.ownerId ,s.state from web_scene s left join web_connection c on s.id = c.sceneId " +
            "where c" +
            ".deviceId " + "in <foreach " + "item='item' collection='deviceIds'" +
            " open='(' separator=',' close=')'> #{item} </foreach> </script>")
    List<Scene> getSceneByDeviceIds(List<Long> deviceIds);

    @Select("<script> select u.id as userId,d.deviceName from web_user u ,web_user_device ud ,web_device d " +
            "where u.deleteFlag = '' and u.id = ud.userId " +
            "and ud.deviceId = d.id and d.id in <foreach item='item' collection='deviceIds' open='('" +
            " separator=',' close=')' >#{item}</foreach> </script>")
    List<UserDeviceVO> getUserDevicesByDeviceIds(List<Long> deviceIds);

    @Insert("<script> insert into web_device(micoDeviceId,deviceClusterId,deviceName,macAddr,description,createDate," +
            "online) values <foreach item='item' collection='newDevices' separator=','> " +
            "(#{item.micoDeviceId},#{item.deviceClusterId},#{item.deviceName},#{item.macAddr},#{item.description}," +
            "#{item.createDate},#{item.online}) " +
            "</foreach> </script>")
    void saveDevices(List<Device> newDevices);

    @Update("<script> <foreach item='item' collection='oldDevices' separator=';'> update web_device set " +
            "deviceClusterId = #{item.deviceClusterId},deviceName = #{item" +
            ".deviceName},online=#{item.online} ,deleteFlag = 0 where micoDeviceId = #{item.micoDeviceId} </foreach> " +
            "</script>")
    void updateDevices(List<Device> oldDevices);

    @Delete("<script> delete from web_device where deleteFlag = #{flag} and micoDeviceId in <foreach item='item' " +
            "collection='devIds' open='(' separator=',' close=')'> #{item} " + "</foreach> " + "</script>")
    void delDevicesAlreadyDelete(List<Long> devIds, int flag);

    @Select("select id from web_device_cluster where name = #{devName}")
    Long getDevClusterIdByName(String devName);

    @Update("update web_device set deviceClusterId = #{devClusterId} where id = #{deviceId}")
    void updateDeviceCluster(Long deviceId, Long devClusterId);

    @Select("select count(1) from web_user_device_cluster udc where udc.deviceClusterId = #{deviceClusterId} and " +
            "udc.role = 1 and userId = (select udc.userId from web_device d inner join " +
            "web_user_device_cluster udc on d.deviceClusterId = udc" +
            ".deviceClusterId and udc.role = 1 where d.id = #{deviceId})")
    Integer isDeviceUserSameOfDevCluster(Long deviceId, Long deviceClusterId);

    @Update("<script>" + " update T_Device set status = #{deleted} where id in (select micoDeviceId from " +
            "web_device where id in <foreach item = 'item' collection='deviceIds' open=" +
            "'(' separator = ',' close=')'> #{item}" + "</foreach>)" + " </script>")
    void updateDevicesFromMicoByIds(List<Long> deviceIds, Integer deleted);

    @Select("select creatorID from T_DeviceCluster where account = #{srcDeviceCluster}")
    Long getCreatorByAccount(String srcDeviceCluster);

    @Update("update web_device set deviceClusterId = #{deviceClusterId} ,deleteFlag = 0 " +
            "where " + "micoDeviceId" + " = #{micoDeviceId}")
    void updateWeb2WebDevice(Long micoDeviceId, Long deviceClusterId);

    @Update("update web_device set deleteFlag = 1 " + "where micoDeviceId = " +
            "#{deviceId}")
    void updateWeb2MicoDeviceByMicoDevId(Long deviceId);

    @Insert("insert into web_device(deviceName,micoDeviceId,deviceClusterId,macAddr,createDate," +
            "online,deleteFlag) " + "values" + "(#{deviceName},#{micoDeviceId}," +
            "#{deviceClusterId},#{macAddr},NOW(),0,0)")
    @Options(useGeneratedKeys = true, keyProperty = "id", keyColumn = "id")
    void saveDeviceFromMico(Device device);

    @Select("select macAddr from T_Device where ID = #{micoDeviceId} and status = 0")
    Device getMicoDeviceById(Long micoDeviceId);

    @Select("select u.id as id,u.username as username from web_user u inner join web_user_device_cluster udc on " +
            "u.id = udc.userId where u.deleteFlag = '' and udc.deviceClusterId " + "= #{deviceClusterId}")
    List<User> getDeviceClusterUserInfo(Long deviceClusterId);

    @Select("select id,micoDeviceId ,deviceClusterId ,deleteFlag from web_device where micoDeviceId = #{micoDeviceId}")
    Device getDeviceByMicoDeviceId(Long micoDeviceId);

    @Delete("delete from web_user_device where deviceId = #{deviceId}")
    void delMyDeviceByDeviceId(Long deviceId);

    @Delete("delete from web_connection where deviceId = #{deviceId}")
    void delSceneConnDevice(Long deviceId);

    @Select("select u.id as id,u.username as username from web_user u inner join web_user_device_cluster udc on u.id" +
            " = udc.userId inner join web_device_cluster dc on dc.id = udc.deviceClusterId where u.deleteFlag " +
            "= '' and dc.name = #{destDeviceCluster}")
    List<User> getDeviceClusterUserInfoByName(String destDeviceCluster);

    @Select("select id from web_device_cluster where name = #{destDeviceCluster}")
    Long getDeviceIdByName(String destDeviceCluster);

//    @Select("select 1 from web_device where micoDeviceId = #{deviceId}")
//    Integer isDeviceExistedByMicoDeviceId(Long deviceId);

    @Update("update web_device set deviceClusterId = #{destDevClusterId} ,deleteFlag = 0" +
            " where micoDeviceId = #{micoDeviceId}")
    void updateDeviceFromMico(Long micoDeviceId, Long destDevClusterId);

    @Select("select id from web_device where micoDeviceId = #{deviceId}")
    Long getDeviceIdByMicoDeviceId(Long deviceId);

    @Update("update web_connection set deviceId = -1 where deviceId = #{deviceId}")
    void updateSceneConnDevice(Long deviceId);

    @Select("<script> select deviceName from web_device where id in <foreach item = 'item' collection='deviceIds' " +
            "open='(' separator = ',' close=')'> #{item} </foreach> </script>")
    List<String> getDeviceNamesByIds(List<Long> deviceIds);

    @Select("<script> select s.id,s.ownerId ,s.state from web_scene s left join web_connection c on s.id = c.sceneId " +
            "where s.ownerId = #{userId} and c" + ".deviceId " + "in <foreach " + "item='item' collection='deviceIds'" +
            " open='(' separator=',' close=')'> #{item} </foreach> </script>")
    List<Scene> getSceneByUidAndDeviceIds(Long userId, List<Long> deviceIds);

    @Update("update web_device set deleteFlag = 1 where id = #{deviceId}")
    void deleteDeviceById(Long deviceId);

    @Update("update web_device set online = #{online} where micoDeviceId = #{deviceId}")
    void updateDeviceOnline(Long deviceId, Integer online);

    @Delete("<script> delete from web_user_device where userId = #{uid} and deviceId in " +
            "<foreach item = 'item' " +
            "collection='deviceIds' " + "open='(' " + "separator = ',' close=')'> #{item}" + "</foreach> </script>")
    void delUserDeviceByUidAndDevIds(Long uid, List<Long> deviceIds);

    @Select("select count(*) from web_user_device_cluster where userId = #{userId} and deviceClusterId = " +
            "#{deviceClusterId}")
    Integer isUserInDeviceCluster(Long userId, Long deviceClusterId);

    @Select("<script> select count(distinct web_user.id) from web_user inner join web_user_device_cluster on web_user" +
            ".id = " + "web_user_device_cluster" +
            ".userId where deviceClusterId = #{deviceClusterId} and web_user.id in <foreach item = 'item' " +
            "collection='userIds' " + "open='(' separator=',' close=')'> " + "#{item} </foreach> </script>")
    Integer getUsersCountByUserIdsAndDevClusterId(List<Long> userIds, Long deviceClusterId);

    @Select("<script> select count(distinct userId) from web_user_device inner join web_user on web_user.id = " +
            "web_user_device.userId where web_user_device.deviceId = #{deviceId} and web_user.id in <foreach " +
            "item='item' collection='userIds' open='(' separator=',' close=')'> #{item} </foreach>" +
            "</script>")
    Integer getUsersCountFromMyDeviceByUserIdsAndDeviceId(List<Long> userIds, Long deviceId);

    @Insert("<script> insert into web_user_device(userId,deviceId) values <foreach item='item' collection='userIds' " +
            "separator=','> " + "(#{item}," + "#{deviceId}) </foreach> </script>")
    void saveUsersDevice(List<Long> userIds, Long deviceId);

    @Delete("<script> delete from web_user_device where deviceId = #{deviceId} and userId in <foreach item='item' " +
            "collection='userIds' open='(' separator=',' close=')'>#{item}</foreach> </script>")
    void delUserDeviceByUserIdsAndDeviceId(List<Long> userIds, Long deviceId);

    @Select("<script> select distinct web_user.id,web_user.username from web_user inner join web_user_device_cluster " +
            "on " + "web_user.id = " +
            "web_user_device_cluster.userId and deviceClusterId = #{deviceClusterId} inner join web_user_device on " +
            "web_user.id = web_user_device.userId  <choose> <when test='auth==\"1\"'>and (web_user_device" +
            ".deviceId is null or web_user_device" + ".deviceId != #{deviceId}) " + "</when> " +
            "<when test='auth==\"0\"'>and web_user_device.deviceId = #{deviceId} </when> <otherwise> " +
            "</otherwise> " + "</choose> " + "</script>")
    List<User> getDeviceAuthUsers(Long deviceClusterId, Long deviceId, Integer auth);

    @Update("<script> update T_Device set status = #{deleted} where ID in <foreach item='item' " +
            "collection='deviceIds' open='(' separator=',' close=')'> #{item} </foreach> </script>")
    void deleteMicoDevices(List<Long> deviceIds, Integer deleted);

}